﻿using BCCommon;
using BCCommon.Enums;
using BCEntity.Sim.Company.Company;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Sim.Company.Company
{
    public class CompanyData : ICompanyData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT CompanyId,CompanySubName,CompanyName,BusinessType,BusinessTypeName,CompanyAdminId,AdminFullName,CreateTime,CompanyStatus,CancelledTime,InUse,StaffCount,
                                        BusinessLicense,BusinessLicenseNo,CompanyAddress,CompanyFinanceNo,CompanyPhone,BankName,CompanyFinanceAddress,EditerId,Editer
                                        FROM Company";

        public CompanyData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<CompanyEntity> Add(CompanyEntity companyEntity)
        {
            string sql = SqlCommands.GetInsertSql("Company", new string[] { "CompanySubName", "CompanyName", "BusinessType","BusinessTypeName","CompanyAdminId", "AdminFullName",
                "CreateTime", "CompanyStatus","BusinessLicense","BusinessLicenseNo","CompanyAddress","CompanyFinanceNo","BankName","CompanyPhone","CompanyFinanceAddress","EditerId","Editer" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanySubName", companyEntity.CompanySubName, DbType.AnsiString, size: 30);
            parameters.Add("?CompanyName", companyEntity.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?BusinessType", companyEntity.BusinessType, DbType.AnsiString);
            parameters.Add("?BusinessTypeName", companyEntity.BusinessTypeName, DbType.AnsiString);
            parameters.Add("?CompanyAdminId", companyEntity.CompanyAdminId, DbType.Int64, size: 20);
            parameters.Add("?AdminFullName", companyEntity.AdminFullName, DbType.AnsiString, size: 20);
            parameters.Add("?CreateTime", companyEntity.CreateTime, DbType.DateTime);
            parameters.Add("?CompanyStatus", companyEntity.CompanyStatus.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessLicense", companyEntity.BusinessLicense, DbType.AnsiString, size: 1000);
            parameters.Add("?BusinessLicenseNo", companyEntity.BusinessLicenseNo.ToUpper(), DbType.AnsiString, size: 50);
            parameters.Add("?CompanyAddress", companyEntity.CompanyAddress, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyFinanceAddress", companyEntity.CompanyFinanceAddress, DbType.AnsiString, size: 1000);
            parameters.Add("?CompanyFinanceNo", companyEntity.CompanyFinanceNo, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyPhone", companyEntity.CompanyPhone, DbType.AnsiString, size: 20);
            parameters.Add("?BankName", companyEntity.BankName, DbType.AnsiString, size: 50);
            parameters.Add("?EditerId", companyEntity.EditerId, DbType.Int64, size: 20);
            parameters.Add("?Editer", companyEntity.Editer, DbType.AnsiString, size: 20);

            sql = string.Format("{0} {1} WHERE CompanyId=LAST_INSERT_ID();", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<CompanyEntity>(sql, parameters,databaseContext.Transaction);
        }

        public async Task<CompanyEntity> Update(CompanyEntity companyEntity)
        {
            DynamicParameters parameters = new DynamicParameters();
            string[] accountStatus = new string[] { "CompanySubName", "CompanyName", "BusinessType","BusinessTypeName", "CompanyAdminId", "AdminFullName","CompanyFinanceAddress",
                    "CreateTime", "CompanyStatus", "BusinessLicense", "BusinessLicenseNo", "CompanyAddress", "CompanyFinanceNo", "BankName", "CompanyPhone" ,"EditerId","Editer" };
            if (companyEntity.CompanyStatus == CompanyStatus.Cancelled)
            {
                accountStatus = new string[] { "CompanySubName", "CompanyName", "BusinessType","BusinessTypeName", "CompanyAdminId", "AdminFullName",
                    "CreateTime", "CompanyStatus", "CancelledTime","CompanyFinanceAddress","EditerId","Editer"  };
                parameters.Add("?CancelledTime", DateTime.Now, DbType.DateTime);
            }
            parameters.Add("?CompanyId", companyEntity.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?CompanySubName", companyEntity.CompanySubName, DbType.AnsiString, size: 30);
            parameters.Add("?CompanyName", companyEntity.CompanyName, DbType.AnsiString, size: 50);
            parameters.Add("?BusinessType", companyEntity.BusinessType.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessTypeName", companyEntity.BusinessTypeName, DbType.AnsiString);
            parameters.Add("?CompanyAdminId", companyEntity.CompanyAdminId, DbType.Int64, size: 20);
            parameters.Add("?AdminFullName", companyEntity.AdminFullName, DbType.AnsiString, size: 20);
            parameters.Add("?CreateTime", companyEntity.CreateTime, DbType.DateTime);
            parameters.Add("?CompanyStatus", companyEntity.CompanyStatus.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessLicense", companyEntity.BusinessLicense, DbType.AnsiString, size: 1000);
            parameters.Add("?BusinessLicenseNo", companyEntity.BusinessLicenseNo.ToUpper(), DbType.AnsiString, size: 50);
            parameters.Add("?CompanyAddress", companyEntity.CompanyAddress, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyFinanceNo", companyEntity.CompanyFinanceNo, DbType.AnsiString, size: 50);
            parameters.Add("?CompanyFinanceAddress", companyEntity.CompanyFinanceAddress, DbType.AnsiString, size: 1000);
            parameters.Add("?CompanyPhone", companyEntity.CompanyPhone, DbType.AnsiString, size: 20);
            parameters.Add("?BankName", companyEntity.BankName, DbType.AnsiString, size: 50);
            parameters.Add("?EditerId", companyEntity.EditerId, DbType.Int64, size: 20);
            parameters.Add("?Editer", companyEntity.Editer, DbType.AnsiString, size: 20);

            string sql = SqlCommands.GetUpdateSql("Company", accountStatus,
               new string[] { "CompanyId" });
            
            sql = string.Format("{0} {1} WHERE CompanyId=?CompanyId;", sql, QUERY);


            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<CompanyEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<IEnumerable<CompanyEntity>> GetList()
        {
            return await this.databaseContext.Slave.QueryAsync<CompanyEntity>(QUERY);
        }

        public async Task<(IEnumerable<CompanyEntity>, int)> Query(string companyBusinessTypeCode, string companyName, CompanyStatus? companyStatus, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            string querySql = @"SELECT CompanyId,CompanySubName,CompanyName,BusinessType,BusinessTypeName,CompanyAdminId,AdminFullName,CreateTime,
                                    CompanyStatus,CancelledTime,InUse,BusinessLicense,BusinessLicenseNo,CompanyAddress,CompanyFinanceNo,
                                    CompanyPhone,BankName,CompanyFinanceAddress,EditerId,Editer,
                                    (SELECT COUNT(1) FROM StaffOrganizationMappingRecord WHERE CompanyId = Company.CompanyId) AS StaffCount
                                FROM Company";
            string querySqlCount = "SELECT COUNT(1) FROM Company";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(companyBusinessTypeCode))
            {
                wheres.Add("BusinessType=?BusinessType");
                parameters.Add("?BusinessType", companyBusinessTypeCode, DbType.AnsiString, size: 50);
            }

            if (!string.IsNullOrWhiteSpace(companyName))
            {
                wheres.Add("CompanyName LIKE CONCAT('%',CONCAT(?CompanyName,'%'))");
                parameters.Add("?CompanyName", companyName, DbType.AnsiString, size: 50);
            }

            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            if (companyStatus.HasValue)
            {
                wheres.Add("CompanyStatus=?CompanyStatus");
                parameters.Add("?CompanyStatus", companyStatus.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; {6} {1}",
                  querySql,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC",
                  querySqlCount);

            return await this.databaseContext.Slave.QueryMultipleAsync<CompanyEntity, int>(sql, parameters);
        }

        public async Task<bool> CheckCompanySubNameExists(string companySubName, int? companyId)
        {
            string sql = @"SELECT 1 FROM Company WHERE CompanySubName=?CompanySubName LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanySubName", companySubName, DbType.AnsiString, size: 30);
            if (companyId.HasValue)
            {
                sql = string.Format("{0} AND CompanyId!={1}", sql, companyId);
                parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            }

            return await this.databaseContext.Slave.ExecuteScalarAsync<bool>(sql, parameters);
        }

        public async Task<CompanyEntity> GetEntity(int companyId)
        {
            string sql = string.Format("{0} WHERE CompanyId=?CompanyId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<CompanyEntity>(sql, parameters);
        }

        public async Task<bool> CheckCompanyHeadExists(long adminId)
        {
            string sql = @"SELECT 1 FROM Company WHERE CompanyAdminId=?CompanyAdminId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyAdminId", adminId, DbType.Int64, size: 20);
            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters) > 0;
        }

        public async Task<bool> CheckCompanyExists(int companyId)
        {
            string sql = @"SELECT 1 FROM Company WHERE CompanyId=?CompanyId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters) > 0;
        }

        public async Task<bool> UpdateInUsed(int companyId)
        {
            string sql = SqlCommands.GetUpdateSql("Company", new string[] { "InUse" }, new string[] { "CompanyId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?InUse", true, DbType.Boolean);
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> UpdateUnused(int companyId)
        {
            string sql = SqlCommands.GetUpdateSql("Company", new string[] { "InUse" }, new string[] { "CompanyId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?InUse", false, DbType.Boolean);
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<bool> Delete(int companyId)
        {
            string sql = @"DELETE FROM Company WHERE CompanyId=?CompanyId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 10);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<int> UpdateCompanyAdminId(long companyId, long adminId)
        {
            return await databaseContext.Master.ExecuteAsync("UPDATE Company SET CompanyAdminId = ?CompanyAdminId WHERE CompanyId=?CompanyId", new { CompanyAdminId = adminId, companyId }, databaseContext.Transaction);
        }
    }
}
